import java.util.*;
import java.sql.*;
import java.io.IOException;
import com.DefectTracking.*;

/*
	this class will interface with the Defect database
*/


public class DefectManager {
	private static final int defectIDIdx = 1;
	private static final int summaryIdx = 2;
	private static final int detailsIdx = 3;
	private static final int detectedByIdx = 4;
	private static final int detectedOnIdx = 5;
	private static final int statusIDIdx = 6;
	private static final int projectIDIdx = 7;
	private static final int closedDateIdx = 8;

	public DefectManager() {
	}
	/*
		this method connects to the defect database and returns a list of all of the defects
	*/
	public List<Defect> getAllOpenDefects() {

		 String searchQuery = "SELECT defect.DefectID, defect.Summary, defect.Details, user.LName, defect.DetectedOn, defectstatus.Description, project.ProjectName, defect.ClosedDate FROM defect, defectstatus, user, project WHERE (defect.StatusID = defectstatus.StatusID) and (defect.DetectedBy = user.UserID) and (defect.ProjectID = project.ProjectID);";
		 List<Defect> defectTable = new ArrayList<Defect>();

		 try {
		 	Class.forName("com.mysql.jdbc.Driver");
		 }//try
		 catch (ClassNotFoundException e) {
		 	System.out.println(e.getMessage());
         }//catch

         try {
	 	 	Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/defect_tracker","root", "root");
	 	 	Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(searchQuery);
            if(rs != null) {
				while(rs.next()) {

					Defect defectTuple = new Defect(rs.getString(defectIDIdx), rs.getString(summaryIdx), rs.getString(detailsIdx), rs.getString(detectedByIdx),
								rs.getString(detectedOnIdx),rs.getString(statusIDIdx),rs.getString(projectIDIdx),rs.getString(closedDateIdx));
					defectTable.add(defectTuple);

				}//while
			}//if
		}//try
		catch (SQLException e) {
            	System.out.println("SQLException occured: " + e.getMessage());
            	e.printStackTrace();
        }
        return defectTable;
	}//getAllOpenDefects


	/*
	This method shall get a list of defect IDs from the userdefect database
	that are assigned to the user passed in(must get the user id from the user database)
	then return the list of those defects from the defect database.
	*/
	public List<Defect> getDefectsForUser(String User) {
		 List<Defect> defectTable = new ArrayList<Defect>();
		 String searchQuery = "SELECT defect.DefectID, defect.Summary, defect.Details, user.LName, defect.DetectedOn, defectstatus.Description, project.ProjectName, defect.ClosedDate FROM defect, defectstatus, user, project WHERE (defect.StatusID = defectstatus.StatusID) and (defect.DetectedBy = user.UserID) and (defect.ProjectID = project.ProjectID) and (defect.AssignedTo = " + User + ");";

		 try {
		 	Class.forName("com.mysql.jdbc.Driver");
		 }//try
		 catch (ClassNotFoundException e) {
		 	System.out.println(e.getMessage());
         }//catch

         try {
	 	 	Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/defect_tracker","root", "root");
	 	 	Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(searchQuery);
            if(rs != null) {
				while(rs.next()) {

					Defect defectTuple = new Defect(rs.getString(defectIDIdx), rs.getString(summaryIdx), rs.getString(detailsIdx), rs.getString(detectedByIdx),
								rs.getString(detectedOnIdx),rs.getString(statusIDIdx),rs.getString(projectIDIdx),rs.getString(closedDateIdx));
					defectTable.add(defectTuple);

				}//while
			}//if
		}//try
		catch (SQLException e) {
            	System.out.println("SQLException occured: " + e.getMessage());
            	e.printStackTrace();
        }
		 return defectTable;

	}


}//class